﻿using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.IO;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Web;
using System.Web.Services;
using System.Threading;

namespace WindStream_Services
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://gleimdev.com/ws")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    [System.Web.Script.Services.ScriptService]

    public class WS_Services : System.Web.Services.WebService
    {
        /*******************************************GET GRAPH DATA BY INSTALL ID***********************************************************/

        [WebMethod()]
        public string GetGraphTitle(string installID, string resolution, string period, int offset)
        {
            string installName = @"Install";

            if(installID.Equals(@"7cae18cd-b16e-4b1b-9e8a-80b52a14abbe"))
            {
                installName = @"Buffalo Bills";
            }
            else if (installID.Equals(@"6c2c0c0e-ce4b-45c4-8762-97fdd3f09f97"))
            {
                installName = @"Helsinki";
            }
            else if (installID.Equals(@"C8B1FF4C-4832-4059-A5B3-25BAF792401F "))
            {
                installName = @"Oslo";
            }
            else if (installID.Equals(@"29BA1081-3BD0-4ADC-A1DE-2664487C6E61 "))
            {
                installName = @"Malaga";
            }

            if (period.Equals(@"WEEK"))
            {
                DateTime startOfWeek = DateTime.Now;
                while (startOfWeek.DayOfWeek != DayOfWeek.Monday)
                {
                    startOfWeek = startOfWeek.AddDays(-1);
                }
                startOfWeek = startOfWeek.AddDays(-7 * offset);
                return installName + @" Energy for Day: " + startOfWeek.Date.ToString("MM/dd/yy");
            }
            else if (period.Equals(@"MONTH"))
            {
                if (resolution.Equals(@"DAY"))
                {
                    DateTime startOfMonth = DateTime.Now.AddMonths(-offset);
                    startOfMonth = new DateTime(startOfMonth.Year, startOfMonth.Month, 1);
                    return installName + @" Energy - " + startOfMonth.Date.ToString("MMM yyyy");
                }
                else
                    return @"NO RESOLUTION FOUND";
            }
            else if (period.Equals(@"DAY"))
            {
                resolution = "HOUR";
                DateTime startTime = DateTime.Now.AddDays(-offset);
                return installName + @" Energy for Day of " + startTime.ToString("MM/dd/yy");
            }
            else
            {
                DateTime thisYear = DateTime.Now.AddYears(-offset);
                return installName + @" Energy - " + thisYear.ToString("yyyy");
            }
        }

        [WebMethod()]
        public DataTable GetGraphData(string installID, string resolution, string period, int offset)
        {
            if(period.Equals(@"DAY"))
            {
                return GetDayByHourGraph(installID, offset);
            }

            if (period.Equals(@"WEEK"))
            {
                return GetWeekByDayGraph(installID, offset);
            }

            if (period.Equals(@"MONTH"))
            {
                if (resolution.Equals(@"DAY"))
                    return GetMonthByDayGraph(installID, offset);
                else
                    return GetMonthByWeekGraph(installID, offset);
            }

            if (period.Equals(@"YEAR"))
            {
                if (resolution.Equals(@"DAY"))
                    return GetYearByDayGraph(installID);
                else if (resolution.Equals(@"WEEK"))
                    return GetYearByWeekGraph(installID, offset);
                else
                    return GetYearByMonthGraph(installID, offset);
            }

            // this is the default value;
            else return null;
        }

        /*******************************************GET POWER GRAPH***********************************************************/

        [WebMethod()]
        public DataTable GetEnergySummary(string installID)
        {
            using (SqlConnection cn = new SqlConnection("Server=tcp:mclv8ptxcr.database.windows.net;Database=tmDB1;User ID=wsAdmin3000;Password=W!nD5tR34mDB;Trusted_Connection=False;Encrypt=True;"))
            {
                cn.Open();

                SqlCommand cmd = new SqlCommand("SELECT * FROM buspower_minute_table WHERE InstallationID = @InstallationID", cn);
                cmd.Parameters.AddWithValue(@"InstallationID", installID);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable installDays = new DataTable();
                da.Fill(installDays);

                DataTable dt = new DataTable("MyTable");

                DataColumn dc0 = new DataColumn("Energy");
                dt.Columns.Add(dc0);

                // 0 = TODAY
                // 1 = THIS WEEK
                // 2 = THIS MONTH
                // 3 = THIS YEAR

                // First, get today's energy, this is row 0
                DataRow dr = dt.NewRow();

                float totalJoules = 0;

                for (int x = 0; x != installDays.Rows.Count; x++)
                {
                    DateTime rowDate = DateTime.Parse(installDays.Rows[x]["UtcMinute"].ToString());
                    dr[0] = 0;
                    
                    if (rowDate.Date == DateTime.Now.Date)
                    {
                        var theseJoules = (float)installDays.Rows[x]["Joules"];

                        if (theseJoules < -36)
                            theseJoules = -36;

                        theseJoules = theseJoules / 3600;
                        totalJoules += theseJoules;
                    }
                }

                        if (totalJoules > 0)
                        {
                            if (totalJoules > 1000)
                            {
                                totalJoules = totalJoules / 1000;
                                var sigFigs = Convert.ToDouble(String.Format("{0:G3}", totalJoules));
                                totalJoules = (float)sigFigs * 1000;
                            }
                            else
                            {
                                var sigFigs = Convert.ToDouble(String.Format("{0:G3}", totalJoules));
                                totalJoules = (float)sigFigs;
                            }
                            dr[0] = totalJoules;
                        }


                dt.Rows.Add(dr);

                // Next, get this week's energy, this is row 1

                DateTime startOfWeek = DateTime.Now.AddDays(-7);
                DateTime endOfWeek = DateTime.Now;
                var weekRow = dt.NewRow();

                float weekWattHours = 0;

                for (int y = 0; y != installDays.Rows.Count; y++)
                {
                    DateTime rowDate = DateTime.Parse(installDays.Rows[y]["UtcMinute"].ToString());
                    if (startOfWeek.Ticks < rowDate.Ticks && rowDate.Ticks < endOfWeek.Ticks)
                    {
                        float theseJoules = (float)installDays.Rows[y]["Joules"];
                        if (theseJoules < -36)
                            theseJoules = -36;

                        theseJoules = theseJoules / 3600;
                        
                        if (theseJoules > 0)
                            weekWattHours += theseJoules;
                        else
                            weekWattHours += 0;
                    }
                }

                if (weekWattHours > 1000)
                {
                    weekWattHours = weekWattHours / 1000;
                    var sigFigs = Convert.ToDouble(String.Format("{0:G3}", weekWattHours));
                    weekWattHours = (float)sigFigs * 1000;
                }
                else
                {
                    var sigFigs = Convert.ToDouble(String.Format("{0:G3}", weekWattHours));
                    weekWattHours = (float)sigFigs;
                }

                weekRow[0] = weekWattHours;
                dt.Rows.Add(weekRow);

                // Next, get the month's energy, this is row 2

                DateTime startOfMonth = DateTime.Now.AddMonths(-1);
                DateTime endOfMonth = DateTime.Now;
                DataRow monthRow = dt.NewRow();

                float monthWattHours = 0;

                for (int y = 0; y != installDays.Rows.Count; y++)
                {
                    DateTime rowDate = DateTime.Parse(installDays.Rows[y]["UtcMinute"].ToString());
                    if (startOfMonth.Ticks < rowDate.Ticks && rowDate.Ticks < endOfMonth.Ticks)
                    {
                        float theseJoules = (float)installDays.Rows[y]["Joules"];
                        if (theseJoules < -36)
                            theseJoules = -36;

                        theseJoules = theseJoules / 3600;
                        
                        if (theseJoules > 0)
                            monthWattHours += theseJoules;
                        else
                            monthWattHours += 0;
                    }
                }

                if (monthWattHours > 1000)
                {
                    monthWattHours = monthWattHours / 1000;
                    var sigFigs = Convert.ToDouble(String.Format("{0:G3}", monthWattHours));
                    monthWattHours = (float)sigFigs * 1000;
                }
                else
                {
                    var sigFigs = Convert.ToDouble(String.Format("{0:G3}", monthWattHours));
                    monthWattHours = (float)sigFigs;
                }

                monthRow[0] = monthWattHours;
                dt.Rows.Add(monthRow);

                // Next, get the entire year's worth of energy, this is row 3

                DateTime startOfYear = DateTime.Now.AddYears(-1);
                DateTime endOfYear = DateTime.Now;
                DataRow yearRow = dt.NewRow();

                float yearWattHours = 0;

                for (int y = 0; y != installDays.Rows.Count; y++)
                {
                    DateTime rowDate = DateTime.Parse(installDays.Rows[y]["UtcMinute"].ToString());
                    if (startOfYear.Ticks < rowDate.Ticks && rowDate.Ticks < endOfYear.Ticks)
                    {
                        float theseWattHours = (float)installDays.Rows[y]["Joules"] / 3600;
                        if (theseWattHours > 0)
                            yearWattHours += theseWattHours;
                        else
                            yearWattHours += 0;
                    }
                }

                if (yearWattHours > 1000)
                {
                    yearWattHours = yearWattHours / 1000;
                    var sigFigs = Convert.ToDouble(String.Format("{0:G3}", yearWattHours));
                    yearWattHours = (float)sigFigs * 1000;
                }
                else
                {
                    var sigFigs = Convert.ToDouble(String.Format("{0:G3}", yearWattHours));
                    yearWattHours = (float)sigFigs;
                }

                yearRow[0] = yearWattHours;
                //yearRow[0] = @"YEAR";
                dt.Rows.Add(yearRow);

                return dt;
            }
        }

        [WebMethod()]
        public DataTable GetPowerGraph(string installID)
        {
            using (SqlConnection cn = new SqlConnection("Server=tcp:mclv8ptxcr.database.windows.net;Database=tmDB1;User ID=wsAdmin3000;Password=W!nD5tR34mDB;Trusted_Connection=False;Encrypt=True;"))
            {
                cn.Open();

                SqlCommand cmd = new SqlCommand("SELECT * FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY InstallationID ORDER BY UtcMinute DESC) ThisMinute,* FROM installation_minute_table) as minList WHERE InstallationID = @InstallationID AND minList.ThisMinute < 100", cn);

                cmd.Parameters.AddWithValue(@"InstallationID", installID);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable installMinutes = new DataTable();
                da.Fill(installMinutes);

                cn.Close();

                DataTable dt = new DataTable("MyTable");

                DataColumn dc0 = new DataColumn("TIME");
                dt.Columns.Add(dc0);

                DataColumn dc1 = new DataColumn("WATTS");
                dt.Columns.Add(dc1);

                DateTime startTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour, 1, 1);

                for(int x=0; x!=60; x++)
                {
                    DateTime thisTime = startTime.AddMinutes(x);
                    DataRow newRow = dt.NewRow();

                    newRow[0] = thisTime.ToString("H:mm");
                    newRow[1] = 0;
                    /*
                    for (int y = 0; y != installMinutes.Rows.Count; y++)
                    {
                        DateTime thisRowTime = DateTime.Parse(installMinutes.Rows[y]["UtcMinute"].ToString());
                        if (thisRowTime.Date == thisTime.Date)
                        {
                            if (thisRowTime.Hour == thisTime.Hour)
                            {
                                if (thisRowTime.Minute == thisTime.Minute)
                                {
                                    // we've found our minute
                                    float theseWatts = 0;
                                    try
                                    {
                                        theseWatts = (float)installMinutes.Rows[y]["Joules"] / 60;
                                    }
                                    catch (Exception e) { };
                                    newRow[1] = theseWatts;
                                }
                            }
                        }
                    } 
                     * */

                    dt.Rows.Add(newRow);
                }

                return dt;
            }
        }

        /*******************************************GET WEEK BY DAY***********************************************************/

        public DataTable GetWeekByDayGraph(string installID, int weekOffset)
        {
            using (SqlConnection cn = new SqlConnection("Server=tcp:mclv8ptxcr.database.windows.net;Database=tmDB1;User ID=wsAdmin3000;Password=W!nD5tR34mDB;Trusted_Connection=False;Encrypt=True;"))
            {
                cn.Open();

                SqlCommand cmd = new SqlCommand("SELECT * FROM buspower_minute_table WHERE InstallationID = @InstallationID", cn);
                cmd.Parameters.AddWithValue(@"InstallationID", installID);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable installDays = new DataTable();
                da.Fill(installDays);

                cn.Close();

                DataTable dt = new DataTable("MyTable");

                DataColumn dc0 = new DataColumn("Day of Week");
                dt.Columns.Add(dc0);

                DataColumn dc1 = new DataColumn("Energy");
                dt.Columns.Add(dc1);

                DateTime startOfWeek = DateTime.Now;

                while(startOfWeek.DayOfWeek != DayOfWeek.Monday)
                {
                    startOfWeek = startOfWeek.AddDays(-1);
                }

                startOfWeek = startOfWeek.AddDays(-7*weekOffset);
               
                for (int x = 0; x < 8; x++)
                {
                    DateTime thisDay = startOfWeek.AddDays(x);
                    DataRow dr = dt.NewRow();
                    dr[0] = thisDay.ToString("ddd");
                    dr[1] = 0;
                    
                    float totalJoules = 0;

                    for (int y = 0; y != installDays.Rows.Count; y++)
                    {
                        DateTime rowDate = DateTime.Parse(installDays.Rows[y]["UtcMinute"].ToString());
                        if (rowDate.Date == thisDay.Date)
                        {
                            float theseJoules = (float)installDays.Rows[y]["Joules"];
                            if (theseJoules < -36)
                                theseJoules = -36;
                        	totalJoules += theseJoules / 3600;           
                        }
                    }

                    if (totalJoules < 0)
                        totalJoules = 0;
                    dr[1] = totalJoules;

                    Console.Out.WriteLine(totalJoules);
                    dt.Rows.Add(dr);
                }

                return dt;
            }
        }

        public DataTable GetDayByHourGraph(string installID, int offset)
        {
            using (SqlConnection cn = new SqlConnection("Server=tcp:mclv8ptxcr.database.windows.net;Database=tmDB1;User ID=wsAdmin3000;Password=W!nD5tR34mDB;Trusted_Connection=False;Encrypt=True;"))
            {
                cn.Open();

                DateTime startDay = DateTime.Today.AddDays(-offset);

                DateTime endDay = startDay.AddDays(1);

                SqlCommand cmd = new SqlCommand("SELECT * FROM buspower_minute_table WHERE InstallationID = @InstallationID AND UtcMinute BETWEEN @start and @end", cn);
                cmd.Parameters.AddWithValue("@InstallationID", installID);
                cmd.Parameters.AddWithValue("@start", startDay);
                cmd.Parameters.AddWithValue("@end", endDay);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable data = new DataTable();
                da.Fill(data);

                cn.Close();

                DataTable dt = new DataTable("MyTable");

                DataColumn dc0 = new DataColumn("Hour");
                dt.Columns.Add(dc0);

                DataColumn dc1 = new DataColumn("Energy");
                dt.Columns.Add(dc1);

                DateTime startOfWeek = DateTime.Now;

                for (int x = 0; x < 24; x++)
                {
                    DateTime thisHour = startDay.AddHours(x);
                    DataRow dr = dt.NewRow();
                    dr[0] = thisHour.ToString("h tt");
                    dr[1] = 0;

                    float totalJoules = 0;

                    for (int y = 0; y != data.Rows.Count; y++)
                    {
                        DateTime rowDate = DateTime.Parse(data.Rows[y]["UtcMinute"].ToString());
                        if (rowDate.Hour == thisHour.Hour-2)
                        {
                            float theseJoules = (float)data.Rows[y]["Joules"];
                            if (theseJoules < -36)
                                theseJoules = -36;
                            totalJoules += theseJoules / 3600;
                        }
                    }

                    if (totalJoules < 0)
                        totalJoules = 0;

                    dr[1] = totalJoules;

                    dt.Rows.Add(dr);
                }
                return dt;
            }
        }

        /*******************************************GET MONTH BY DAY***********************************************************/

        public DataTable GetMonthByDayGraph(string installID, int offset)
        {
            using (SqlConnection cn = new SqlConnection("Server=tcp:mclv8ptxcr.database.windows.net;Database=tmDB1;User ID=wsAdmin3000;Password=W!nD5tR34mDB;Trusted_Connection=False;Encrypt=True;"))
            {
                cn.Open();

                SqlCommand cmd = new SqlCommand("SELECT * FROM buspower_minute_table WHERE InstallationID = @InstallationID", cn);
                cmd.Parameters.AddWithValue(@"InstallationID", installID);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable installDays = new DataTable();
                da.Fill(installDays);

                DataTable dt = new DataTable("MyTable");

                DataColumn dc0 = new DataColumn("Day");
                dt.Columns.Add(dc0);

                DataColumn dc1 = new DataColumn("Energy");
                dt.Columns.Add(dc1);

                DateTime startOfMonth = DateTime.Now.AddMonths(-offset);

                startOfMonth = new DateTime(startOfMonth.Year, startOfMonth.Month, 1);

                DateTime endOfMonth = new DateTime(startOfMonth.Year, startOfMonth.Month, 1).AddMonths(1);

                DateTime thisDate = startOfMonth;

                while(thisDate.Date != endOfMonth)
                {
                    DataRow dr = dt.NewRow();
                    dr[0] = thisDate.ToString("dd");
                    dr[1] = 0;

                    float totalJoules = 0;

                    for (int y = 0; y != installDays.Rows.Count; y++)
                    {
                        DateTime rowDate = DateTime.Parse(installDays.Rows[y]["UtcMinute"].ToString());
                        if (rowDate.Date == thisDate.Date)
                        {
                            float theseJoules = (float)installDays.Rows[y]["Joules"];
                            if (theseJoules < -36)
                                theseJoules = -36;
                            totalJoules += theseJoules / 3600;
                        }
                    }

                    if (totalJoules < 0)
                        totalJoules = 0;
                    dr[1] = totalJoules;

                    dt.Rows.Add(dr);
                    thisDate = thisDate.AddDays(1);
                }
                
                return dt;

            }
        }

        /*******************************************GET MONTH BY WEEK***********************************************************/

        public DataTable GetMonthByWeekGraph(string installID, int offset)
        {
            using (SqlConnection cn = new SqlConnection("Server=tcp:mclv8ptxcr.database.windows.net;Database=tmDB1;User ID=wsAdmin3000;Password=W!nD5tR34mDB;Trusted_Connection=False;Encrypt=True;"))
            {
                cn.Open();

                SqlCommand cmd = new SqlCommand("SELECT * FROM buspower_minute_table WHERE InstallationID = @InstallationID", cn);
                cmd.Parameters.AddWithValue(@"InstallationID", installID);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable installDays = new DataTable();
                da.Fill(installDays);

                DataTable dt = new DataTable("MyTable");

                DataColumn dc0 = new DataColumn("Week");
                dt.Columns.Add(dc0);

                DataColumn dc1 = new DataColumn("Energy");
                dt.Columns.Add(dc1);

                for (int x = 4; x > -1; x--)
                {
                    DateTime startOfWeek = DateTime.Now.AddDays(-x * 7);
                    DateTime endOfWeek = DateTime.Now.AddDays(-x * 7 + 7);
                    DataRow dr = dt.NewRow();

                    float weekJoules = 0;
                    float numReadings = 0;

                    for (int y = 0; y != installDays.Rows.Count; y++)
                    {
                        DateTime rowDate = DateTime.Parse(installDays.Rows[y]["UtcMinute"].ToString());
                        if (startOfWeek.Ticks < rowDate.Ticks && rowDate.Ticks < endOfWeek.Ticks)
                        {
                            numReadings++;
                            float theseJoules = (float)installDays.Rows[y]["Joules"];
                            if (theseJoules < -36)
                                theseJoules = -36;
                            theseJoules = theseJoules / 3600;
                       
                            weekJoules += theseJoules;
                        }
                    }

                    if(numReadings > 0)
                        weekJoules = weekJoules * (7 / numReadings);


                    dr[0] = startOfWeek.ToString("MM/dd/yy") + "-" + endOfWeek.ToString("MM/dd/yy");
                    dr[1] = weekJoules;

                    dt.Rows.Add(dr);
                }

                return dt;
            }
        }

        /*******************************************GET YEAR BY DAY***********************************************************/

        public DataTable GetYearByDayGraph(string installID)
        {
            using (SqlConnection cn = new SqlConnection("Server=tcp:mclv8ptxcr.database.windows.net;Database=tmDB1;User ID=wsAdmin3000;Password=W!nD5tR34mDB;Trusted_Connection=False;Encrypt=True;"))
            {
                cn.Open();

                SqlCommand cmd = new SqlCommand("SELECT * FROM buspower_minute_table WHERE InstallationID = @InstallationID", cn);
                cmd.Parameters.AddWithValue(@"InstallationID", installID);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable installDays = new DataTable();
                da.Fill(installDays);

                DataTable dt = new DataTable("MyTable");

                DataColumn dc0 = new DataColumn("Day");
                dt.Columns.Add(dc0);

                DataColumn dc1 = new DataColumn("Energy");
                dt.Columns.Add(dc1);

                DateTime startOfYear = DateTime.Now;

                startOfYear = new DateTime(startOfYear.Year, 1, 1);

                DateTime endOfYear = startOfYear.AddYears(1);

                DateTime thisDate = startOfYear;

                while (thisDate.Date != endOfYear)
                {
                    DataRow dr = dt.NewRow();
                    dr[0] = thisDate.ToString("dd");
                    dr[1] = 0;

                    float totalJoules = 0;

                    for (int y = 0; y != installDays.Rows.Count; y++)
                    {
                        DateTime rowDate = DateTime.Parse(installDays.Rows[y]["UtcMinute"].ToString());
                        if (rowDate.Date == thisDate.Date)
                        {
                            float theseJoules = (float)installDays.Rows[y]["Joules"];
                            if (theseJoules < -36)
                                theseJoules = -36;
                            totalJoules += theseJoules / 3600;
                        }
                    }

                    if (totalJoules < 0)
                        totalJoules = 0;
                    dr[1] = totalJoules;

                    dt.Rows.Add(dr);
                    thisDate = thisDate.AddDays(1);
                }

                return dt;
            }
        }

        /*******************************************GET YEAR BY WEEK***********************************************************/

        public DataTable GetYearByWeekGraph(string installID, int offset)
        {
            using (SqlConnection cn = new SqlConnection("Server=tcp:mclv8ptxcr.database.windows.net;Database=tmDB1;User ID=wsAdmin3000;Password=W!nD5tR34mDB;Trusted_Connection=False;Encrypt=True;"))
            {
                cn.Open();

                SqlCommand cmd = new SqlCommand("SELECT * FROM buspower_minute_table WHERE InstallationID = @InstallationID", cn);
                cmd.Parameters.AddWithValue(@"InstallationID", installID);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable installDays = new DataTable();
                da.Fill(installDays);

                DataTable dt = new DataTable("MyTable");

                DataColumn dc0 = new DataColumn("Week");
                dt.Columns.Add(dc0);

                DataColumn dc1 = new DataColumn("Energy");
                dt.Columns.Add(dc1);

                DateTime dayInYear = DateTime.Now.AddYears(-offset);
                DateTime startOfYear = new DateTime(dayInYear.Year, 1, 1);

                for (int x = 0; x < 52; x++)
                {
                    DateTime startOfWeek = startOfYear.AddDays(7 * x);
                    DateTime endOfWeek = startOfWeek.AddDays(7);
                    DataRow dr = dt.NewRow();

                    float weekJoules = 0;
                    //var numReadings = 0;

                    for (int y = 0; y != installDays.Rows.Count; y++)
                    {
                        DateTime rowDate = DateTime.Parse(installDays.Rows[y]["UtcMinute"].ToString());
                        if (startOfWeek.Ticks < rowDate.Ticks && rowDate.Ticks < endOfWeek.Ticks)
                        {
                        //    numReadings++;
                            float theseJoules = (float)installDays.Rows[y]["Joules"];
                            if (theseJoules < -36)
                                theseJoules = -36;
                            theseJoules = theseJoules / 3600;
                            weekJoules += theseJoules;
                        }
                    }

                    //if (numReadings > 0)
                    //    weekJoules = weekJoules * (7 / numReadings);

                    dr[0] = "Week " + (x+1);
                    dr[1] = weekJoules;
                    dt.Rows.Add(dr);
                }

                return dt;
            }
        }

        /*******************************************GET YEAR BY MONTH***********************************************************/

        public DataTable GetYearByMonthGraph(string installID, int offset)
        {
            using (SqlConnection cn = new SqlConnection("Server=tcp:mclv8ptxcr.database.windows.net;Database=tmDB1;User ID=wsAdmin3000;Password=W!nD5tR34mDB;Trusted_Connection=False;Encrypt=True;"))
            {
                cn.Open();

                SqlCommand cmd = new SqlCommand("SELECT * FROM buspower_minute_table WHERE InstallationID = @InstallationID", cn);
                cmd.Parameters.AddWithValue(@"InstallationID", installID);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable installDays = new DataTable();
                da.Fill(installDays);

                DataTable dt = new DataTable("MyTable");

                DataColumn dc0 = new DataColumn("Month");
                dt.Columns.Add(dc0);

                DataColumn dc1 = new DataColumn("Energy");
                dt.Columns.Add(dc1);

                DateTime dayInYear = DateTime.Now.AddYears(-offset);

                DateTime startOfYear = new DateTime(dayInYear.Year, 1, 1);

                for (int x = 0; x < 12; x++)
                {
                    DateTime startOfMonth = startOfYear.AddMonths(x);
                    DateTime endOfMonth = startOfMonth.AddMonths(1);
                    DataRow dr = dt.NewRow();

                    float monthJoules = 0;

                    for (int y = 0; y != installDays.Rows.Count; y++)
                    {
                        DateTime rowDate = DateTime.Parse(installDays.Rows[y]["UtcMinute"].ToString());
                        if (startOfMonth.Ticks < rowDate.Ticks && rowDate.Ticks < endOfMonth.Ticks)
                        {
                            float theseJoules = (float)installDays.Rows[y]["Joules"];
                            if(theseJoules < -36)
                                theseJoules = -36;
                            theseJoules = theseJoules / 3600;
                            
                            monthJoules += theseJoules;
                            
                        }
                    }

                    dr[0] = startOfMonth.ToString("MMM");
                    dr[1] = monthJoules;
                    dt.Rows.Add(dr);
                }

                return dt;
            }
        }

        /*******************************************GET MONTH BY DAY***********************************************************/

        public DataTable GetMonthInYear(string installID, int offset, string month)
        {
            using (SqlConnection cn = new SqlConnection("Server=tcp:mclv8ptxcr.database.windows.net;Database=tmDB1;User ID=wsAdmin3000;Password=W!nD5tR34mDB;Trusted_Connection=False;Encrypt=True;"))
            {
                cn.Open();

                SqlCommand cmd = new SqlCommand("SELECT * FROM installation_day_table WHERE InstallationID = @InstallationID", cn);
                cmd.Parameters.AddWithValue(@"InstallationID", installID);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable installDays = new DataTable();
                da.Fill(installDays);

                DataTable dt = new DataTable("MyTable");

                DataColumn dc0 = new DataColumn("Day");
                dt.Columns.Add(dc0);

                DataColumn dc1 = new DataColumn("Energy");
                dt.Columns.Add(dc1);

                DateTime selectedYear = DateTime.Now.AddYears(-offset);
                DateTime startOfMonth = DateTime.Now;

                /* THIS SHOULD GET THE MONTH OF THE INPUT MONTH STRING
                for(int monthIndex=1; monthIndex < 13; monthIndex++)
                {
                    startOfMonth = new DateTime(selectedYear.Year, monthIndex, 1);
                    if (startOfMonth.ToString("MMM").Equals(month))
                    {
                        monthIndex = 12;
                    }
                }
                */
                startOfMonth = new DateTime(selectedYear.Year, 1, 1);
                DateTime endOfMonth = startOfMonth.AddMonths(1);

                DateTime thisDate = startOfMonth;

                while (thisDate.Date != endOfMonth.Date)
                {
                    DataRow dr = dt.NewRow();
                    dr[0] = thisDate.ToString("dd");
                    dr[1] = 0;

                    for (int y = 0; y != installDays.Rows.Count; y++)
                    {
                        DateTime rowDate = DateTime.Parse(installDays.Rows[y]["UtcMinute"].ToString());

                        if (rowDate.Date == thisDate.Date)
                        {
                            float theseJoules = (float)installDays.Rows[y]["Joules"] / 3600;
                            if (theseJoules > 0)
                                dr[1] = theseJoules;
                            else
                                dr[1] = 0;
                        }
                    }
                    dt.Rows.Add(dr);
                    thisDate = thisDate.AddDays(1);
                }

                return dt;
            }
        }
    }
}